const mysql = require('mysql2/promise')
require('dotenv').config()

async function createDatabase() {
  let connection
  
  try {
    console.log('连接MySQL服务器...')
    
    // 连接到MySQL服务器（不指定数据库）
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'root'
    })
    
    console.log('MySQL连接成功')
    
    const dbName = process.env.DB_NAME || 'yundayin'
    
    // 检查数据库是否存在
    const [databases] = await connection.execute('SHOW DATABASES')
    const dbExists = databases.some(db => db.Database === dbName)
    
    if (dbExists) {
      console.log(`数据库 ${dbName} 已存在`)
    } else {
      // 创建数据库
      await connection.execute(`CREATE DATABASE ${dbName} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`)
      console.log(`数据库 ${dbName} 创建成功`)
    }
    
    // 重新连接到目标数据库
    await connection.end()
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'root',
      database: dbName
    })
    
    // 检查products表是否存在
    const [tables] = await connection.execute("SHOW TABLES LIKE 'products'")
    
    if (tables.length === 0) {
      console.log('products表不存在，将由Sequelize自动创建')
    } else {
      console.log('products表已存在，检查字段...')
      
      // 检查字段
      const [columns] = await connection.execute('DESCRIBE products')
      const columnNames = columns.map(col => col.Field)
      
      console.log('现有字段:', columnNames)
      
      // 检查是否缺少image_url字段
      if (!columnNames.includes('image_url')) {
        console.log('添加缺失的image_url字段...')
        await connection.execute(`
          ALTER TABLE products 
          ADD COLUMN image_url VARCHAR(500) NULL COMMENT '商品图片'
        `)
        console.log('image_url字段添加成功')
      }
      
      // 检查其他可能缺失的字段
      const requiredFields = {
        'paper_size': "ENUM('A4', 'A3', 'A5', 'other') NULL COMMENT '纸张尺寸'",
        'paper_type': "VARCHAR(50) NULL COMMENT '纸张类型'",
        'color_type': "ENUM('bw', 'color', 'both') NULL DEFAULT 'bw' COMMENT '颜色类型'",
        'stock_warning': "INT NULL DEFAULT 100 COMMENT '库存预警值'",
        'is_new': "TINYINT NOT NULL DEFAULT 0 COMMENT '是否新品'"
      }
      
      for (const [fieldName, fieldDef] of Object.entries(requiredFields)) {
        if (!columnNames.includes(fieldName)) {
          console.log(`添加缺失的${fieldName}字段...`)
          await connection.execute(`ALTER TABLE products ADD COLUMN ${fieldName} ${fieldDef}`)
          console.log(`${fieldName}字段添加成功`)
        }
      }
    }
    
    console.log('数据库准备完成!')
    return true
    
  } catch (error) {
    console.error('数据库操作失败:', error.message)
    
    if (error.code === 'ECONNREFUSED') {
      console.error('无法连接到MySQL服务器，请确保MySQL正在运行')
      console.error('如果使用XAMPP/WAMP，请启动MySQL服务')
      console.error('如果使用Docker，请运行: docker-compose up -d mysql')
    }
    
    return false
  } finally {
    if (connection) {
      await connection.end()
    }
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  createDatabase()
    .then(success => {
      if (success) {
        console.log('操作完成')
        process.exit(0)
      } else {
        console.log('操作失败')
        process.exit(1)
      }
    })
}

module.exports = { createDatabase }